<?php
namespace app\home\controller;
use think\Controller;
use think\Cookie;
use think\Db;

/***
 *计划表单导出
 */
class Plan extends Controller
{
    /***表单导出**/
    function export_plan($Id){
        /**获取数据库**/
        $plan_data=Db::name('plan')->where('id',$Id)->find();
        $plan_table_data=Db::name('plan_table')->where('father_table_id',$Id)->select();

        /**Excel操作**/
        $col=[
            'A','B','C','D','E','F','G',
            'H','I','J','K','L','M','N',
            '0','P','Q','R','S','T','U',
            'V','W','X','Y','Z'
        ];
        $table_title=[
            '序号','实验内容','学时数',
            '序号','实验内容','学时数',
        ];
        $excel_table=new \PHPExcel();
        //设置sheet
        $excel_table->setActiveSheetIndex(0);

        //设置列宽
        for($i=0;$i<=7;$i++)
        {
            $excel_table->getActiveSheet()->getColumnDimension($col[$i])->setWidth(-1);
        }


        //合并单元格
        $excel_table->getActiveSheet()
            ->mergeCells('A1:H1')
            ->mergeCells('C2:G2')
            ->mergeCells('A4:B4')
            ->mergeCells('C4:D4')
            ->mergeCells('E4:F4')
            ->mergeCells('G4:H4')
            ->mergeCells('A5:B5')
            ->mergeCells('C5:D5')
            ->mergeCells('E5:F5')
            ->mergeCells('G5:H5')
            ->mergeCells('A6:B6')
            ->mergeCells('C6:D6')
            ->mergeCells('E6:F6')
            ->mergeCells('G6:H6')
            ->mergeCells('A7:B7')
            ->mergeCells('C7:D7')
            ->mergeCells('E7:F7')
            ->mergeCells('G7:H7')
        ;
        //设置表头
        $excel_table->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
        $excel_table->getActiveSheet()->getStyle('A1')
            ->getFont()
            ->setSize(16)
            ->setName('黑体');
        $excel_table->getActiveSheet()->getStyle('A1')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel_table->getActiveSheet()->setCellValue('A1','湖北工程学院计算机实验教学示范中心课程实验计划表');
        //统计表时间
        $excel_table->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
        $excel_table->getActiveSheet()->getStyle('C2')
            ->getFont()
            ->setSize(14)
            ->setName('Times New Roman');
        $excel_table->getActiveSheet()->getStyle('C2')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel_table->getActiveSheet()->setCellValue('C2','('.$plan_data['year'].'学年度'.$plan_data['term'].')');

        //统计表信息
        $excel_table->getActiveSheet()->getStyle('A4:G7')->getFont()->setSize(15)->setName('宋体');
        $excel_table->getActiveSheet()->getStyle('C4:C7')->getFont()->setUnderline(true);
        $excel_table->getActiveSheet()->getStyle('G4:G7')->getFont()->setUnderline(true);
        $excel_table->getActiveSheet()->getStyle('A4:A7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel_table->getActiveSheet()->getStyle('E4:E7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel_table->getActiveSheet()->getStyle('C4:C7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel_table->getActiveSheet()->getStyle('G4:G7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel_table->getActiveSheet()
            ->setCellValue('A4','课程名称：')
            ->setCellValue('C4',$plan_data['class_name'])
            ->setCellValue('E4','开课专业：')
            ->setCellValue('G4',$plan_data['major'])
            ->setCellValue('A5','开课班级：')
            ->setCellValue('C5',$plan_data['class'])
            ->setCellValue('E5','学生人数：')
            ->setCellValue('G5',$plan_data['stu_num'])
            ->setCellValue('A6','授课教师：')
            ->setCellValue('C6',$plan_data['teacher'])
            ->setCellValue('E6','实验个数：')
            ->setCellValue('G6',$plan_data['experiment_num'])
            ->setCellValue('A7','指导老师：')
            ->setCellValue('C7',$plan_data['instructor'])
            ->setCellValue('E7','实验时数：')
            ->setCellValue('G7',$plan_data['hour']);

        //统计表内容中的表头
        //设置行高
        $excel_table->getActiveSheet()->getRowDimension(8)->setRowHeight(22.9);
        $excel_table->getActiveSheet()
            ->mergeCells('B8:C8')
            ->mergeCells('F8:G8');
        $excel_table->getActiveSheet()->getStyle('A8:H8')
            ->getFont()
            ->setBold(true)
            ->setSize(14)
            ->setName('黑体');
        $excel_table->getActiveSheet()->getStyle('A8:H8')
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setWrapText(true);
        $excel_table->getActiveSheet()
            ->setCellValue('A8','序号')
            ->setCellValue('B8','实验内容')
            ->setCellValue('D8','学时数')
            ->setCellValue('E8','序号')
            ->setCellValue('F8','实验内容')
            ->setCellValue('H8','学时数')
        ;
        $total=count($plan_table_data);
        if($total<20) $total=20;
        $total=$total%2 ? $total+1 : $total;   //是奇数就加一个

        /**设置表格区域**/
        //提前设置边框线
        $style= array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THICK
                )
            )
        );
        $excel_table->getActiveSheet()->getStyle('A9:H'.($total/2+8))
            ->applyFromArray($style);
        $excel_table->getActiveSheet()->getStyle('A9:H'.(8+$total/2))
            ->getFont()
            ->setBold(true)
            ->setSize(10.5)
            ->setName('宋体');
        $excel_table->getActiveSheet()->getStyle('A9:H'.(8+$total/2))
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setWrapText(true);

        //填入序号
        for($i=1;$i<=$total/2;$i++)
        {
            $excel_table->getActiveSheet()->getRowDimension($i+8)->setRowHeight(26.7);
            $excel_table->getActiveSheet()
                ->mergeCells('F'.($i+8).':G'.($i+8))
                ->mergeCells('B'.($i+8).':C'.($i+8));
            $excel_table->getActiveSheet()
                ->setCellValue('A'.($i+8),$i)
                ->setCellValue('E'.($i+8),$i+$total/2);
        }


        /**填入数据**/
        $num=0;
        foreach ($plan_table_data as $table_datum)
        {
            $num++;
            if((int)(($num-1)/($total/2))==0)
            {
                $excel_table->getActiveSheet()
                    ->setCellValue('B'.($num+8),$table_datum['experiment_content'])
                    ->setCellValue('D'.($num+8),$table_datum['hour']);
            }else{
                $excel_table->getActiveSheet()
                    ->setCellValue('F'.($num-$total/2+8),$table_datum['experiment_content'])
                    ->setCellValue('H'.($num-$total/2+8),$table_datum['hour']);
            }

        }

        //设置表尾注
        //合并单元格
        $excel_table->getActiveSheet()
            ->mergeCells('B'.($total/2+9).':H'.($total/2+9))
            ->mergeCells('B'.($total/2+10).':H'.($total/2+10));

        //设置行高（自适应）
        $excel_table->getActiveSheet()->getRowDimension($total/2+9)->setRowHeight(-1);
        $excel_table->getActiveSheet()->getRowDimension($total/2+10)->setRowHeight(40);
        $excel_table->getActiveSheet()->getStyle('A'.($total/2+9).':H'.($total/2+10))
            ->getFont()
            ->setSize(14)
            ->setName('宋体');
        $excel_table->getActiveSheet()->getStyle('A'.($total/2+9).':H'.($total/2+10))
            ->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
            ->setWrapText(true);
        $excel_table->getActiveSheet()
            ->setCellValue('A'.($total/2+9), "说明：")
            ->setCellValue('B'.($total/2+9),'1、根据实验大纲组织实验内容；')
            ->setCellValue('B'.($total/2+10),'2、实验计划表由授课教师与实验指导教师一起填写，于开学后第二周内交实验室，以便统筹安排上机时间。');

        header('pragma:public');
        header('Content-Disposition:attachment;filename=实验计划表'.time().'.xlsx');
        header('Cache-Control: max-age=0');

        $excel_writer=\PHPExcel_IOFactory::createWriter($excel_table,'Excel2007');
        $excel_writer->save('php://output');
    }
    function get_person_info(){
        $cookie=new Cookie();
        $safecode=$cookie->get('safecode');
        $data=Db::name('user')->where('safecode',$safecode)->find();
        return $data;
    }


    /**计划表的实验课程**/
    public function plan_table(){
        $this->get_person_info();
        $id=input('get.Id');
        $this->assign('Id',$id);
        return $this->fetch('plan/plan_table',[],['__PUBLIC__'=>'/public/static']);
    }

    public function plan_table_json()  //获取统计表内容接口
    {
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $Id=input('get.Id');
        $data=DB::name('plan_table')->where(['father_table_id'=>$Id])->select();
        $father=$Id;
        $father_data=Db::name('plan')->where('id',$father)->find();
        $father_uid=$father_data['uid'];
        if($uid==$father_uid){
            return json($data);
        }

    }
    public function insert_plan_table()    //新增页面
    {
        $this->assign('Id',input('get.Id'));
        return $this->fetch('plan/insert_plan_table',[],['__PUBLIC__'=>'/public/static']);
    }
    public function into_plan_table()  //增加操作
    {
        $person_data=$this->get_person_info();
        $person_uid=$person_data['Id'];
        $data=input('post.');
        $father_id=$data['father_table_id'];
        $father_data=Db::name('plan')->where('id',$father_id)->find();
        if($father_data['uid']!=$person_uid){
            $this->error('无权操作');
        }else{
            $res=Db::name('plan_table')->insert($data);
            if($res){
                $this->success("添加成功");
            }else{
                $this->error("添加失败");
            }
        }

    }
    public function edit_plan_table()  //编辑计划表
    {
        $person_data=$this->get_person_info();
        $person_uid=$person_data['Id'];
        $data=input('post.');
        $field=$data['field'];
        $Id=$data['id'];
        $father_data=Db::name('plan')->where('id',$data['father_table_id'])->find();
        $data=$data[$field];
        if($father_data['uid']!=$person_uid){
            return "error";
        }else{
            $sql=Db::name('plan_table')->where('Id',$Id)->update([$field=>$data]);
            if($sql){
                return "success";
            }else{
                return "error";
            }
        }
    }
    public function delete_plan_table()    //删除计划表中的实验课程
    {
        $person_data=$this->get_person_info();
        $person_uid=$person_data['Id'];
        $data=file_get_contents('php://input');
        $data=json_decode($data,true);
        $delete_list=array();
        foreach ($data as $datum)
        {
            $one=Db::name('plan_table')->where('id',$datum['id'])->find();
            $father_id=$one['father_table_id'];
            $father_data=Db::name('plan')->where('id',$father_id)->find();
            if(!empty($father_data)&&$father_data['uid']==$person_uid)
            {
                $delete_list[]=$datum["id"];
            }
        }
        $sql=Db::name('plan_table')->delete($delete_list);
        if($sql){
            return json([
                'msg'=>'success'
            ]);
        }else{
            return json([
                'msg'=>'error'
            ]);
        }
    }


    //计划表
    public function plan() //计划表数据
    {
        return $this->fetch('plan/plan',[],['__PUBLIC__'=>'/public/static']);
    }
    public function plan_json()    //获取统计表数据
    {
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $data=Db('plan')->where('uid',$uid)->select();
        return json($data);
    }
    public function insert_plan(){
        return $this->fetch('plan/insert_plan',[],['__PUBLIC__'=>'/public/static']);
    }
    public function into_plan()  //增加操作
    {
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $data=input('post.');
        $data['uid']=$uid;
        if(strtotime($data['year'][0])<strtotime($data['year'][1]))
            $data['year']=$data['year'][0].'-'.$data['year'][1];
        else
            $data['year']=$data['year'][1].'-'.$data['year'][0];
        switch ($data['term']){
            case 1:
                $data['term']="第一学期";
                break;
            case 2:
                $data['term']="第二学期";
                break;
        }
        $res=Db::name('plan')->insert($data);
        if($res){
            $this->success("添加成功");
        }else{
            $this->error("添加失败");
        }
    }
    public function edit_plan()    //编辑计划表信息
    {
        $data=input('post.');
        $field=$data['field'];  //要修改的字段
        //获取当前用户信息
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $Id=$data['id'];
        $plan_data=Db::name('plan')->where('id',$Id)->find();
        if($plan_data['uid']==$uid){
            $sql=Db::name('plan')->where('id',$Id)->update([$field=>$data[$field]]);
            if($sql)
            {
                return "success";
            }else{
                return "error";
            }
        }else{
            return "error";
        }
    }
    public function delete_plan(){
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $data=file_get_contents('php://input');
        $data=json_decode($data,true);
        $delete_list=array();
        foreach ($data as $datum){
            $one=Db::name('plan')->where('id',$datum['id'])->find();
            if($one['uid']==$uid)
                $delete_list[]=$datum['id'];
        }
        $sql=Db::name('plan')->delete($delete_list);
        if($sql){
            return json([
                'msg'=>'success'
            ]);
        }else{
            return json([
                'msg'=>'error'
            ]);
        }
    }




    /**********导出******/
    public function export()    //导出统计表
    {
        $Id=input('get.Id');
        $user_data=$this->get_person_info();
        $uid=$user_data['Id'];
        $count_data=Db::name('plan')->where('id',$Id)->find();
        if($uid!=$count_data['uid'])
        {
            $this->error('越权操作');
            return;
        }
        $this->export_plan($Id);
    }
}

